E-R Modeling Exercises

• CD-U has many store locations - each store location has an address and one or more telephone numbers
• It has employees, some of whom might work at multiple store locations. 
• Each store has a manager, who is also an employee. Some managers manage multiple stores.
• Each CD has a title, date of release and information about the artists featured on the CD. 
• Each artist has a name and the label (like Sony) with whom they have signed
• Each store has an inventory of CDs.
• Not all store locations stock all CDs.
• Some CDs, like older CDs,  may not be available at any store
• The price of a CD may be different at each location
• Each store also tracks the quantity of each CD that it has
• CD-U also wants to track the songs on each CD it sells
• Each song has a title, length, and contributing artist(s). 
• Song titles need not be unique. 
• The same song may appear on different CDs, potentially recorded by different artists.
• CD-U also tracks its customers (name, phone number), and their purchases (date, which CD they bought, and from which store).

A trucking company called Truckers is responsible for picking up shipments from the warehouses of a retail chain called Maze Brothers and delivering the shipments to individual retail store locations of Maze Brothers. Currently there are 6 warehouse locations and 45 Maze Brothers retail stores. A truck may carry several shipments during a single trip, which is identified by a Trip#, and delivers those shipments to multiple stores. Each shipment is identified by a Shipment# and includes data on shipment volume, weight, destination, etc. Trucks have different capacities for both the volumes they can hold and the weights they can carry. The Truckers company currently has 150 trucks, and a truck makes 3 to 4 trips each week. A database - to be used by both Truckers and Maze Brothers - is being designed to keep track of truck usage and deliveries and to help in scheduling trucks to provide timely deliveries to the stores.

Design an ER model for the above application. State all assumptions.

Give an ER design to model the following scenario ...

• Patients are identified by an SSN, and their names, addresses and ages must be recorded.
• Doctors are identified by an SSN. For each doctor, the name, specialty and years of experience must be recorded.
• Each pharmacy has a name, address and phone number. A pharmacy must have a manager.
• A pharmacist is identified by an SSN, he/she can only work for one pharmacy. For each pharmacist, the name, qualification must be recorded.
• For each drug, the trade name and formula must be recorded.
• Every patient has a primary physician. Every doctor has at least one patient.
• Each pharmacy sells several drugs, and has a price for each. A drug could be sold at several pharmacies, and the price could vary between pharmacies.
• Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription has a date and quantity associated with it.

State all assumptions used in developing your data model.

Give an ER design to model the following scenario ...

• a driver has an employee id, a name and a birthday
• a bus has a make, model, registration number and capacity 
  (e.g. a Volvo 425D bus which can carry 60 passengers, with registration MO-3235)
• a bus may also have features (e.g. air-conditioned, disabled access, video screens, etc.)
• a bus-stop (normally abbreviated to simply stop) is a defined place where a bus may stop to pick up or set down passengers
• each stop has a name, which is displayed on the timetable (e.g. ``Central Station'')
• each stop also has a location (street address) (e.g. ``North side of Eddy Avenue'')
• a route describes a sequence of one or more stops that a bus will follow
• each route has a number (e.g. route 372, from Coogee to Circular Quay)
• each route has a direction: ``inbound'' or ``outbound'' 
  (e.g. 372 Coogee to Circular Quay is ``inbound'', 372 Circular Quay to Coogee is ``outbound'')
• for each stop on a route, we note how long it should take to reach that stop from the first stop
• the time-to-reach the first stop on a route is zero
• stops may be used on several routes; some stops may not (currently) be used on any route
• a schedule specifies an instance of a route (e.g. the 372 departing Circular Quay at 10:05am)
• schedules are used to produce the timetables displayed on bus-stops
• a service denotes a specific bus running on a specific schedule on a particular day with a particular driver
• services are used internally by the bus company to keep track of bus/driver allocations
• the number of minutes that each bus service arrives late at its final stop needs to be recorded

State all assumptions used in developing your data model.

• The database maintains records for owners, teams, players, and games. 
• An owner may own several teams in the league. 
• A player may only play for one team at one time. However, players may play for different teams during their career. 
• The league wants to know which players played for which team during a certain period of time. 
• For each season, the league wants to know the win-loss records of the teams. 
• The database also maintains details for each game played, including date, location, and scores.
• The database also maintains information about which player played in which game and their stats for that game, like running yards, touchdowns scored, number of interceptions,number of fumbles etc.